package com.apes.fn.tms.repository;

import com.apes.fn.tms.model.BranchLineItem;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BranchLineItemRepository extends CustomRepository<BranchLineItem, String> {

    @Query(value = " SELECT B.departure_station, B.terminal " +
            "          FROM tms_branch_line_item A, tms_branch_line B " +
            "         WHERE A.branch_line_id = B.id AND B.line_type = 'BranchLine' and a.phy_Depot_id = :phyDepot AND B.departure_station IS NOT NULL ", nativeQuery = true)
    List<BranchLineItem> findExistsBranchLine(@Param("phyDepot") String phyDepot);

    /**
     * 根据发货仓库找线路发货站点对应的收/门店站点, 多个线路时terminal/departure_station用来接头
     * @param departureStation
     * @return
     */
    @Query(value = " SELECT A.* " +
            "   FROM (SELECT (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) " +
            "                     FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station, " +
            "                 (SELECT (SELECT name FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station_name, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT NAME FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal_NAME, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT ID FROM mas_phy_depot WHERE ID = B.phy_Depot_id) " +
            "                   ELSE NULL END phy_Depot_id, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT NAME FROM mas_phy_depot WHERE ID = b.phy_Depot_id) " +
            "                   ELSE NULL END phy_depot_NAME, " +
//            "                 (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) " +
//            "                     FROM tms_site J " +
//            "                    WHERE ID = A.terminal) terminal, " +
//            "                 (SELECT (SELECT NAME FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J " +
//            "                    WHERE ID = A.terminal) terminal_NAME, " +
//            "                 (SELECT ID FROM mas_phy_depot WHERE ID = b.phy_Depot_id) phy_depot_id, " +
//            "                 (SELECT NAME FROM mas_phy_depot WHERE ID = b.phy_Depot_id) phy_depot_NAME,  " +
            "                 A.LINE_TYPE, A.ID BRANCH_LINE_ID, A.NAME branch_line_name, SYSDATE + B.TIME_TOTAL / 24 / 60 estimateDeliveryTime" +
            "            FROM tms_branch_line A, tms_branch_line_item b " +
            "           where a.id = b.branch_line_id(+) AND A.VALID = 1) A " +
            "  WHERE a.departure_station = :departureStation  ", nativeQuery = true)
    List<Map> findFHBranchLine(@Param("departureStation") String departureStation);

    /**
     * 根据收货仓库找到最后的线路，返回对应的发货仓库
     * @return
     */
    @Query(value = " SELECT A.* " +
            "   FROM (SELECT (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) " +
            "                     FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station, " +
            "                 (SELECT (SELECT name FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station_name, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT NAME FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal_NAME, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT ID FROM mas_phy_depot WHERE ID = B.phy_Depot_id) " +
            "                   ELSE NULL END phy_Depot_id, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT NAME FROM mas_phy_depot WHERE ID = b.phy_Depot_id) " +
            "                   ELSE NULL END phy_depot_NAME, " +
            "                 A.LINE_TYPE, A.ID BRANCH_LINE_ID, A.NAME branch_line_name, SYSDATE + B.TIME_TOTAL / 24 / 60 estimateDeliveryTime " +
            "            FROM tms_branch_line A, tms_branch_line_item b " +
            "           where a.id = b.branch_line_id(+) AND A.VALID = 1) A " +
            "  WHERE (a.terminal = :terminal or A.phy_depot_id = :terminal) AND A.branch_line_id NOT IN (:branchLineId) ", nativeQuery = true)
    List<Map> findSHBranchLine(@Param("terminal") String terminal, @Param("branchLineId") Set<String> branchLineId);

    @Query(value = " SELECT A.* " +
            "   FROM (SELECT (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) " +
            "                     FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station, " +
            "                 (SELECT (SELECT name FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J " +
            "                    WHERE ID = A.departure_station) departure_station_name, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal, " +
            "                 CASE WHEN A.LINE_TYPE = 'MainLine' THEN " +
            "                    (SELECT (SELECT NAME FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J WHERE ID = A.terminal) " +
            "                   ELSE NULL END terminal_NAME, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT ID FROM mas_phy_depot WHERE ID = B.phy_Depot_id) " +
            "                   ELSE NULL END phy_Depot_id, " +
            "                 CASE WHEN A.LINE_TYPE = 'BranchLine' THEN " +
            "                    (SELECT NAME FROM mas_phy_depot WHERE ID = b.phy_Depot_id) " +
            "                   ELSE NULL END phy_depot_NAME, " +
//            "                 (SELECT (SELECT ID FROM mas_phy_depot WHERE ID = J.phy_Depot_id) " +
//            "                     FROM tms_site J " +
//            "                    WHERE ID = A.terminal) terminal, " +
//            "                 (SELECT (SELECT NAME FROM mas_phy_depot WHERE ID = J.phy_Depot_id) FROM tms_site J " +
//            "                    WHERE ID = A.terminal) terminal_NAME, " +
//            "                 (SELECT ID FROM mas_phy_depot WHERE ID = B.phy_Depot_id) phy_Depot_id, " +
//            "                 (SELECT NAME FROM mas_phy_depot WHERE ID = b.phy_Depot_id) phy_depot_NAME,  " +
            "                 A.LINE_TYPE, A.ID BRANCH_LINE_ID, A.NAME branch_line_name, SYSDATE + B.TIME_TOTAL / 24 / 60 estimateDeliveryTime" +
            "            FROM tms_branch_line A, tms_branch_line_item b " +
            "           where a.id = b.branch_line_id(+) AND A.VALID = 1) A " +
            "  WHERE (a.terminal = :terminal or A.phy_depot_id = :terminal) AND A.branch_line_id IN (:branchLineId) ", nativeQuery = true)
    List<Map> findExistsSHBranchLine(@Param("terminal") String terminal, @Param("branchLineId") Set<String> branchLineId);
}
